package demo;

import cn.hutool.core.collection.CollectionUtil;
import com.lkcoffee.demo.domain.ColMappingDto;
import com.lkcoffee.demo.domain.NormalSqlStructureDto;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.lang3.StringUtils;


import java.io.BufferedWriter;
import java.io.IOException;
import java.io.StringReader;
import java.io.UncheckedIOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static com.lkcoffee.demo.utils.SqlToMarkdownUtils.convertSqlToMarkdown;
import static com.lkcoffee.demo.utils.SqlToMarkdownUtils.findFollowingElement;
import static com.lkcoffee.demo.utils.SqlToMarkdownUtils.readSqlFile;
import static com.lkcoffee.demo.utils.SqlToMarkdownUtils.writeMarkdownFile;

public class SqlToMarkdownConverter {

    private static final String SQL_FILE_PATH = "D:\\code\\java-test\\demo1\\src\\main\\resources\\1.sql";
    private static final String MARKDOWN_FILE_PATH = "D:\\code\\java-test\\demo1\\src\\main\\resources";

    public static void main(String[] args) {
        String sql = readSqlFile(SQL_FILE_PATH);
        List<String> sqlList = CollectionUtil.toList(sql.split(";"));
        sqlList.forEach(item -> {
            NormalSqlStructureDto normalSqlStructureDto = convertSqlToMarkdown(item);
            Path path = Paths.get(MARKDOWN_FILE_PATH, normalSqlStructureDto.getTableNames() + ".md");
            writeMarkdownFile(path.toString(), normalSqlStructureDto);
        });

    }



    /**
     * 解析sql结构
     *
     * @param sql
     * @param isAlias true|false 是否使用别称<br> eg. 【s_id as id】 => 【id】<br>
     * @return
     * @throws JSQLParserException
     */
//    public static NormalSqlStructureDto getStructure(String sql, boolean isAlias) throws JSQLParserException {
//        NormalSqlStructureDto normalSqlStructureDto = new NormalSqlStructureDto();
//        if (StringUtils.isEmpty(sql)) {
//           return null;
//        }
//        normalSqlStructureDto.setSql(sql);
//        sql = sql.replaceAll("(\\$\\{\\w*\\})|(\\{\\{\\w+\\}\\})", "''");
//        // 1.解析表名
//        CCJSqlParserManager parserManager = new CCJSqlParserManager();
//        // 解析SQL为Statement对象
//        Statement statement = parserManager.parse(new StringReader(sql));
//
//        // 创建表名发现者对象
//        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
//        // 获取到表名列表
//        List<String> tableNameList = tablesNamesFinder.getTableList(statement);
//        normalSqlStructureDto.setTableNames(tableNameList);
//        // 表别名映射
//        Map<String, Object> tableMapping = new HashMap<>();
//        tableNameList.forEach(i -> tableMapping.put(i, i));
//        // 字段和表的映射
//        List<ColMappingDto> colMappingList = new ArrayList<>();
//
//        // 2.解析查询元素 列，函数等
//        Select select = (Select) CCJSqlParserUtil.parse(sql);
//        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
////        FromItem fromItem = plainSelect.getFromItem();
////        System.out.println(JSON.toJSON(fromItem).toString());
////        fromItem.getAlias();
//
//        List<SelectItem<>> selectItems = plainSelect.getSelectItems();
//        List<String> columnList = new ArrayList<>();
//        if (!CollectionUtil.isEmpty(selectItems)) {
//            for (SelectItem selectItem : selectItems) {
//                ColMappingDto colMapping = new ColMappingDto();
//                String columnName = "";
//                String tblAlias = "";
//                try {
//                    if (selectItem instanceof SelectExpressionItem) {
//                        SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
//                        Alias alias = selectExpressionItem.getAlias();
//                        Expression expression = selectExpressionItem.getExpression();
//                        // FIXME: 2023/3/9
//                        Column col = ((Column) expression);
//                        Table colTbl = col.getTable();
//                        if (Objects.nonNull(colTbl)) {
//                            tblAlias = colTbl.getName();
//                        }
//                        buildTblMapping(tableMapping, sql, tblAlias);
//                        if (!isAlias) {
//                            columnName = selectItem.toString();
//                        } else if (expression instanceof CaseExpression) {
//                            // case表达式
//                            columnName = alias.getName();
//                        } else if (expression instanceof LongValue || expression instanceof StringValue || expression instanceof DateValue || expression instanceof DoubleValue) {
//                            // 值表达式
//                            columnName = Objects.nonNull(alias.getName()) ? alias.getName() : expression.getASTNode().jjtGetValue().toString();
//                        } else if (expression instanceof TimeKeyExpression) {
//                            // 日期
//                            columnName = alias.getName();
//                        } else {
//                            if (alias != null) {
//                                columnName = alias.getName();
//                            } else {
//                                SimpleNode node = expression.getASTNode();
//                                Object value = node.jjtGetValue();
//                                if (value instanceof Column) {
//                                    columnName = ((Column) value).getColumnName();
//                                } else if (value instanceof Function) {
//                                    columnName = value.toString();
//                                } else {
//                                    // 增加对select 'aaa' from table; 的支持
//                                    columnName = String.valueOf(value);
//                                    columnName = columnName.replace("'", "");
//                                    columnName = columnName.replace("\"", "");
//                                    columnName = columnName.replace("`", "");
//                                }
//                            }
//                        }
//
//                        columnName = columnName.replace("'", "");
//                        columnName = columnName.replace("\"", "");
//                        columnName = columnName.replace("`", "");
//
//                        colMapping.setName(col.getColumnName());
//                        if (Objects.nonNull(alias) && StringUtils.isNotEmpty(alias.getName())) {
//                            colMapping.setAlias(alias.getName());
//                        }
//                        colMapping.setTable(tableMapping.get(tblAlias));
//
//                    } else if (selectItem instanceof AllTableColumns) {
//                        AllTableColumns allTableColumns = (AllTableColumns) selectItem;
//                        columnName = allTableColumns.toString();
//                        if (columnName.indexOf(".") > -1) {
//                            tblAlias = columnName.substring(0, columnName.indexOf(".")).trim();
//                            buildTblMapping(tableMapping, sql, tblAlias);
//                            colMapping.setTable(tableMapping.get(tblAlias));
//                        } else {
//                            colMapping.setTable(tableNameList);
//                        }
//                        colMapping.setName(columnName);
//                    } else if (selectItem.toString().equals("*")) {
//                        columnName = selectItem.toString();
//                        colMapping.setName(columnName);
//                        colMapping.setTable(tableNameList);
//                    } else {
//                        columnName = selectItem.toString();
//                        colMapping.setName(columnName);
//                        colMapping.setType("varchar");
//                    }
//                } catch (Exception e) {
//                    columnName = selectItem.toString();
//                    colMapping.setName(columnName);
//                    colMapping.setType("varchar");
//                    colMapping.setTable(null);
//                }
//
//                columnList.add(columnName);
//                colMappingList.add(colMapping);
//            }
//            normalSqlStructureDto.setSelectItems(columnList);
//            normalSqlStructureDto.setColMappings(colMappingList);
//        }
//
//        return normalSqlStructureDto;
//    }

}
